<?php
/**
 * Created by PhpStorm.
 * User: Aliaksei Lazerka
 * Date: 10/21/14
 * Time: 8:19 PM
 */

$loggerFactory = new \log\LoggerFactory(array(dirname(__FILE__) . '/../conf/log.xml'));

//$logger = $loggerFactory->getLoggerByName('systemLogger');
$logger = $loggerFactory->getLoggerByName('browserLogger');

$connectionProperties = new \base\ConnectionProperties();
$connectionProperties->setDbURL(getConfValue("db.url"));
$connectionProperties->setDbName(getConfValue("db.name"));
$connectionProperties->setUsername(getConfValue("db.user"));
$connectionProperties->setPassword(getConfValue("db.pass"));

$transaction = new \mysql\MySQLTransaction();

$dbActions = new \mysql\MySQLDBActions();

global $connection;
$connection = new \base\Connection($transaction, $connectionProperties, $dbActions, $logger);

global $sqlQuery;
$sqlQuery = new \sql\NamedSQLStorage(new SQLParser());

/**
 * @param $fieldName
 * @param $fieldType
 * @param $fieldAlias
 * @param $fieldDefault [optional]
 * @return mixed
 */
function addWireField($fieldName, $fieldType, $fieldAlias, $fieldDefault = null)
{
  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");
  $params->addParam("fieldName", $fieldName);
  $params->addParam("fieldType", $fieldType);
  $params->addParam("fieldAlias", $fieldAlias);

  if ($fieldDefault) {
    $params->addParam("default", $fieldDefault);
  } else {
    $params->addParam("default", "");
  }

  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("addWireField", $params)));

  if ($result) {
    $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("addWireAlias", $params)));
  }
}


/**
 * @param $listElemId
 * @param string $fields [optional]
 */
function createRootItemView($listElemId, $fields)
{
  global $connection;
  global $sqlQuery;

  $tmpSet = $connection->executeStringQueryInTransaction("SELECT TABLE_NAME FROM CATALOG_SECTIONS WHERE ID = (SELECT CS_ID FROM CATALOG_LIST_ELEMS WHERE ID = '" . $listElemId . "')");

  if ($table = mysql_fetch_assoc($tmpSet)) {

    $params = new \sql\Params(":");
    $fields = 'ID, CLS_ID' . $fields;
    $params->addParam("fields", $fields);
    $params->addParam('viewName', $table['TABLE_NAME'] . "_" . $listElemId . "_v");
    $params->addParam('table', $table['TABLE_NAME']);

    $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("createRootItemView", $params)));
  }
}

/**
 * @param $sectionId
 * @param $elemTitle
 * @param string[] $fields [optional]
 */
function createListElement($sectionId, $elemTitle, $fields = null)
{
  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");
  $params->addParam("title", $elemTitle);
  $params->addParam("sectionId", $sectionId);

  $lastId = $connection->executeQueryWithCallback(new \sql\SimpleSQLQuery($sqlQuery->getQuery("createListItem", $params)), new \mysql\LastIdInnerCallback());
  if ($lastId)
    createRootItemView($lastId, $fields);
}


/**
 * @param string[] $fields
 * @param string[] $defaultFields [optional]
 * @return string
 */
function generateFields($fields, $defaultFields = null)
{
  $fieldsString = "";
  if ($defaultFields) {
    foreach ($defaultFields as $defField) {
      $fieldsString .= ", " . $defField;
    }
  }
  foreach ($fields as $field) {
    $fieldsString .= ", " . $field;
  }
  if ($fieldsString) {
    $fieldsString = substr($fieldsString, 1);
  }
  return $fieldsString;
}


function deleteAllWireViews($db)
{

  global $connection;
  global $sqlQuery;


  $params = new \sql\Params(":");
  $params->addParam("db", $db);

  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("selectAllWireViews", $params)));
  while ($row = mysql_fetch_assoc($result)) {
    $name = array_values($row)[0];
    if (strpos($name, 'wires_') === 0) {
      $params->addParam("view", $name);
      $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("deleteView", $params)));
    }
  }
}


function deleteWiresListElement($elemId)
{
  global $connection;
  global $sqlQuery;


  $params = new \sql\Params(":");
  $params->addParam("elemId", $elemId);
  $params->addParam("view", "wires_" . $elemId . "_v");


  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("deleteListElement", $params)));
  if ($result) {
    $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("deleteView", $params)));
  }
}


/**
 * @param $listSubId
 * @param string[] $values should be as array with keys as table column names and values as values
 * @return mixed
 */
function addWire($listSubId, $values)
{

  global $connection;
  global $sqlQuery;

  $ignoredFields = array('ID');

  $params = new \sql\Params(":");

  $fields = getViewFields(getListElemIdBySub($listSubId), $ignoredFields);

  $columns = "";
  $valuesString = "";

  foreach ($fields as $field) {
    if ($values[$field]) {
      $columns .= ", " . $field;
      $valuesString .= ", '" . $values[$field] . "'";
    }
  }

  $columns = substr($columns, 1);
  $valuesString = substr($valuesString, 1);

  $params->addParam("fields", $columns);
  $params->addParam("values", $valuesString);

  return $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("addWire", $params)));
}


function getViewFields($listElemId, $ignoredFields)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");
  $params->addParam("listElemId", $listElemId);
  $fields = array();
  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getViewFields", $params)));
  while ($row = mysql_fetch_assoc($result)) {
    if (!in_array($row['COLUMN_NAME'], $ignoredFields))
      $fields[] = $row['COLUMN_NAME'];
  }
  return $fields;
}

function getMainCategories()
{


  global $connection;
  global $sqlQuery;

  return $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getCategories", null)));
}

function getListRoots($sectionId)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");
  $params->addParam("sectionId", $sectionId);

  return $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getListRoots", $params)));
}

function getListSubElems($listRootId)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");
  $params->addParam("listRootId", $listRootId);

  return $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getListElems", $params)));
}

function changeMainCategoryTitle($categoryId, $title)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");
  $params->addParam("sectionId", $categoryId);
  $params->addParam("title", $title);

  $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("changeCategoryTitle", $params)));
}


function getTableAliases($sectionId)
{
  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");
  $params->addParam("sectionId", $sectionId);

  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getTableAliases", $params)));

  $aliases = array();
  while ($row = mysql_fetch_assoc($result)) {
    $aliases[$row['FIELD_NAME']] = $row['FIELD_ALIAS'];
  }

  return $aliases;
}

function addListSubElement($listRoot, $subTitle)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");
  $params->addParam("listRootId", $listRoot);
  $params->addParam("subTitle", $subTitle);

  $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("addListSubElement", $params)));
}

function getProductFields($listRoot, $sectionId)
{

  global $connection;
  global $sqlQuery;

  $defaultFields = array('ID', 'CLS_ID');

  $params = new \sql\Params(":");

  $fieldArray = null;

  $tableAliases = getTableAliases($sectionId);
  $tmpSet = $connection->executeStringQueryInTransaction("SELECT TABLE_NAME FROM CATALOG_SECTIONS WHERE ID = '" . $sectionId . "'");

  if ($table = mysql_fetch_assoc($tmpSet)) {
    $view = $table['TABLE_NAME'] . "_" . $listRoot . "_v";
    $params->addParam("table", $view);
    $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getTableDescription", $params)));

    $fieldArray = array();
    while ($row = mysql_fetch_assoc($result)) {
      if (!in_array($row['Field'], $defaultFields)) {
        $fieldArray[$row['Field']] = array($row['Type'], $tableAliases[$row['Field']]);
      }
    }
  }

  return $fieldArray;
}

function addProduct($sectionId, $listRoot, $listSub, $post)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");

  $fields = "CLS_ID";

  $values = "'" . $listSub . "'";

  $tmpSet = $connection->executeStringQueryInTransaction("SELECT TABLE_NAME FROM CATALOG_SECTIONS WHERE ID = '" . $sectionId . "'");

  if ($table = mysql_fetch_assoc($tmpSet)) {
    $view = $table['TABLE_NAME'] . "_" . $listRoot . "_v";
    $view = strtolower($view);

    foreach ($post as $key => $value) {
      $fields .= ", " . $key;
      $values .= ", '" . $value . "'";
    }

    $params->addParam("view", $view);
    $params->addParam("fields", $fields);
    $params->addParam("values", $values);

    $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("addProduct", $params)));
  }
}

function addTableFieldInDB($sectionId, $fieldAlias, $fieldType)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");

  $tmpSet = $connection->executeStringQueryInTransaction("SELECT TABLE_NAME FROM CATALOG_SECTIONS WHERE ID = '" . $sectionId . "'");


  if ($table = mysql_fetch_assoc($tmpSet)) {
    $table = $table['TABLE_NAME'];

    $fieldCountSet = $connection->executeStringQueryInTransaction("DESCRIBE " . $table . ";");
    $fieldPostfix = mysql_num_rows($fieldCountSet);
    $fieldName = "field_" . $fieldPostfix;

    $params->addParam("table", $table);
    $params->addParam("fieldName", $fieldName);
    $params->addParam("fieldType", $fieldType);

    $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("addField", $params)));

    if ($result) {

      $params->addParam("fieldAlias", $fieldAlias);
      $params->addParam("sectionId", $sectionId);

      $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("addAlias", $params)));
    }

  }
}

function changeListRoot($listRootId, $listRootTitle)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");

  $params->addParam("listRootId", $listRootId);
  $params->addParam("title", $listRootTitle);

  $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("editRoot", $params)));
}

function deleteListRoot($listRootId)
{
  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");

  $params->addParam("listRootId", $listRootId);

  $tmpSet = $connection->executeStringQueryInTransaction("SELECT TABLE_NAME FROM CATALOG_SECTIONS WHERE ID = (SELECT CS_ID FROM CATALOG_LIST_ELEMS WHERE ID = '" . $listRootId . "')");

  $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("deleteRoot", $params)));

  if ($table = mysql_fetch_assoc($tmpSet)) {
    $table = $table['TABLE_NAME'];

    $view = $table . "_" . $listRootId . "_v";
    $params->addParam("view", $view);

    $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("deleteRootView", $params)));
  }
}

function editListSub($listSubId, $listSubTitle)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");

  $params->addParam("listSubId", $listSubId);
  $params->addParam("listSubTitle", $listSubTitle);

  $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("editListSub", $params)));

}

function deleteListSub($listSubId)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");

  $params->addParam("listSubId", $listSubId);

  $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("delListSub", $params)));
}

function deleteProduct($sectionId, $productId)
{

  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");

  $tmpSet = $connection->executeStringQueryInTransaction("SELECT TABLE_NAME FROM CATALOG_SECTIONS WHERE ID = '" . $sectionId . "'");


  if ($table = mysql_fetch_assoc($tmpSet)) {
    $table = $table['TABLE_NAME'];

    $params->addParam("tableName", $table);
    $params->addParam("productId", $productId);

    $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("delProduct", $params)));
  }

}

function getProducts($listSubId, $sectionId)
{
  global $connection;
  global $sqlQuery;

  $result = null;

  $params = new \sql\Params(":");

  $tmpSet = $connection->executeStringQueryInTransaction("SELECT TABLE_NAME FROM CATALOG_SECTIONS WHERE ID = '" . $sectionId . "'");


  if ($table = mysql_fetch_assoc($tmpSet)) {
    $table = $table['TABLE_NAME'];

    $params->addParam("table", $table);
    $params->addParam("listSubId", $listSubId);

    $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getProducts", $params)));
  }
  return $result;
}

function getAllUsers()
{
  global $connection;
  global $sqlQuery;

  $result = null;

  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getAllUsers",null)));

  return $result;
}

function getAllRoles()
{
  global $connection;
  global $sqlQuery;

  $result = null;

  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getAllRoles",null)));

  return $result;
}

function editUser($roleId, $uid)
{
  global $connection;
  global $sqlQuery;

  $params = new \sql\Params(":");

  $params->addParam("roleId", $roleId);
  $params->addParam("uid", $uid);

  $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("editUser",$params)));
}

function delUser($uid)
{
  global $connection;
  global $sqlQuery;

  $result = null;

  $params = new \sql\Params(":");

  $params->addParam("uid", $uid);

  $connection->executeQueryUpdateInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("delUser",$params)));
}

function getUsrPass($usrName){
  global $connection;
  global $sqlQuery;

  $result = null;

  $params = new \sql\Params(":");

  $params->addParam("usrName", $usrName);

  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getUsrPass",$params)));

  return $result;
}

function getUsrNameByMail($email){
  global $connection;
  global $sqlQuery;

  $result = null;

  $params = new \sql\Params(":");

  $params->addParam("email", $email);

  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getUsrNameByMail",$params)));

  return $result;
}

function getUsrIdRoleByPassAndUsrName($pass, $usrName){
  global $connection;
  global $sqlQuery;

  $result = null;

  $params = new \sql\Params(":");

  $params->addParam("pass", $pass);
  $params->addParam("usrName", $usrName);

  $result = $connection->executeQueryInTransaction(new \sql\SimpleSQLQuery($sqlQuery->getQuery("getUsrIdRoleByPassAndUsrName",$params)));

  return mysql_fetch_assoc($result);
}
